package com.key.controller;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.key.model.Organization;
import com.key.tools.ConnectionPool;

@Controller
@RequestMapping("/org")
public class OrgController {

	@RequestMapping("/query")
	public ModelAndView query(Organization org, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		List<Organization> list = new ArrayList<Organization>();
		try {
			int index = org.getPageNum();
			int number = org.getEveryPage();
			int startindex = (index - 1) * number;
			PreparedStatement ps = ct
					.prepareStatement("select * from t_group where code <> 'admin' and groupname like '%"
							+ org.getGroupname()
							+ "%' and code like '%"
							+ org.getGroupcode()
							+ "%' and status = '1' limit ?,?");
			ps.setInt(1, startindex);
			ps.setInt(2, number);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Organization o = new Organization();
				o.setGroupname(rs.getString("groupname"));
				o.setId(rs.getString("id"));
				o.setGroupcode(rs.getString("code"));
				list.add(o);
			}

			PreparedStatement ps2 = ct
					.prepareStatement("select count(*) as cc from t_group where code <> 'admin' and groupname like '%"
							+ org.getGroupname()
							+ "%' and code like '%"
							+ org.getGroupcode() + "%' and status = '1'");
			ResultSet rs2 = ps2.executeQuery();
			if (rs2.next()) {
				org.setTotalCount(rs2.getInt("cc"));
			}

			request.setAttribute("org", org);
			request.setAttribute("list", list);
			rs.close();
			ps.close();
		} catch (Exception e) {
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
			}
		}
		return new ModelAndView("jsp/org/list");
	}

	@RequestMapping("/new")
	public ModelAndView newOrg(Organization org, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		try {
			PreparedStatement ps = ct
					.prepareStatement("insert into t_group(groupname,code) values(?,?)");
			ps.setString(1, org.getGroupname());
			ps.setString(2, org.getGroupcode());
			int r = ps.executeUpdate();
			if (r > 0) {
				request.setAttribute("result", true);
			} else {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", "");
			}
			ps.close();
		} catch (Exception e) {
			request.setAttribute("result", false);
			request.setAttribute("errorMessage", e.getMessage());
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", e.getMessage());
			}
		}
		return new ModelAndView("/jsp/sys/result");
	}

	@RequestMapping("/delete")
	public ModelAndView deleteOrg(String id, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		try {
			PreparedStatement ps = ct
					.prepareStatement("update t_group set status = 0 where id = ?");
			ps.setString(1, id);
			int r = ps.executeUpdate();
			if (r > 0) {
				request.setAttribute("result", true);
			} else {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", "");
			}
			ps.close();
		} catch (Exception e) {
			request.setAttribute("result", false);
			request.setAttribute("errorMessage", e.getMessage());
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", e.getMessage());
			}
		}
		return new ModelAndView("/jsp/sys/result");
	}

	@RequestMapping("/edit")
	public ModelAndView editOrg(String id, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		try {
			PreparedStatement ps = ct
					.prepareStatement("select * from t_group where id = ?");
			ps.setString(1, id);
			ResultSet rs = ps.executeQuery();
			Organization org = new Organization();
			org.setId(id);
			if (rs.next()) {
				org.setGroupcode(rs.getString("code"));
				org.setGroupname(rs.getString("groupname"));
			}
			request.setAttribute("org", org);
			rs.close();
			ps.close();
		} catch (Exception e) {
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
			}
		}
		return new ModelAndView("/jsp/org/edit");
	}

	@RequestMapping("/modify")
	public ModelAndView modifyOrg(Organization org, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		try {
			PreparedStatement ps = ct
					.prepareStatement("update t_group set groupname = ?,code=? where id=?");
			ps.setString(1, org.getGroupname());
			ps.setString(2, org.getGroupcode());
			ps.setString(3, org.getId());
			int r = ps.executeUpdate();
			if (r > 0) {
				request.setAttribute("result", true);
			} else {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", "");
			}
			ps.close();
		} catch (Exception e) {
			request.setAttribute("result", false);
			request.setAttribute("errorMessage", e.getMessage());
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", e.getMessage());
			}
		}
		return new ModelAndView("/jsp/sys/result");
	}

	@RequestMapping("/select")
	public ModelAndView selectOrg(HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		try {
			PreparedStatement ps = ct
					.prepareStatement("select * from t_group where status = '1'");
			ResultSet rs = ps.executeQuery();
			StringBuilder sb = new StringBuilder();
			sb.append("[");
			int i = 0;
			while (rs.next()) {
				if (i > 0) {
					sb.append(",");
				}
				sb.append("{\"groupId\":\"");
				sb.append(rs.getString("id"));
				sb.append("\"");
				sb.append(",\"groupName\":\"");
				sb.append(rs.getString("groupname"));
				sb.append("\"}");
				i++;
			}
			sb.append("]");
			request.setAttribute("info", sb.toString());
			rs.close();
			ps.close();
		} catch (Exception e) {
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
			}
		}
		return new ModelAndView("/jsp/sys/info");
	}

	@RequestMapping("/search")
	public ModelAndView search(Organization org, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		List<Organization> list = new ArrayList<Organization>();
		try {
			int index = org.getPageNum();
			int number = org.getEveryPage();
			int startindex = (index - 1) * number;
			PreparedStatement ps = ct
					.prepareStatement("select * from t_group where groupname like '%"
							+ org.getGroupname()
							+ "%' and code like '%"
							+ org.getGroupcode()
							+ "%' and status = '1' limit ?,?");
			ps.setInt(1, startindex);
			ps.setInt(2, number);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Organization o = new Organization();
				o.setGroupname(rs.getString("groupname"));
				o.setId(rs.getString("id"));
				o.setGroupcode(rs.getString("code"));
				list.add(o);
			}

			PreparedStatement ps2 = ct
					.prepareStatement("select count(*) as cc from t_group where groupname like '%"
							+ org.getGroupname()
							+ "%' and code like '%"
							+ org.getGroupcode() + "%' and status = '1'");
			ResultSet rs2 = ps2.executeQuery();
			if (rs2.next()) {
				org.setTotalCount(rs2.getInt("cc"));
			}

			request.setAttribute("org", org);
			request.setAttribute("list", list);
			rs.close();
			ps.close();
		} catch (Exception e) {
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
			}
		}
		return new ModelAndView("jsp/sys/orgSelect");
	}
}
